package com.piotrs10.bsk.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.piotrs10.bsk.model.permissions.ObiektDB;
import com.piotrs10.bsk.model.permissions.PermissionEntry;
import com.piotrs10.bsk.model.permissions.SimplePermissionEntry;
import com.piotrs10.bsk.model.permissions.TypUprawnien;
import com.piotrs10.bsk.model.permissions.Uprawnienia;
import com.piotrs10.bsk.model.permissions.UprawnieniaSimple;
import com.piotrs10.bsk.model.permissions.Uzytkownik;

public class PermissionsDao extends BaseDao {

	private Connection connection = null;

	private final String PERMISSIONS_QUERY_WITH_LOCK = "SELECT \r\n"
			+ "  o.id,\r\n"
			+ "  o.nazwa,\r\n"
			+ "  (SELECT count(*) FROM uprawnienia u\r\n"
			+ "   WHERE u.typ_uprawnien_id = 1 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id) can_select,\r\n"
			+ "   (SELECT count(*)\r\n"
			+ "    FROM  uzytkownik g\r\n"
			+ "    where g.login=? and \r\n"
			+ "          (G.ADMINISTRATOR='T' or\r\n"
			+ "           exists(select * from uprawnienia u\r\n"
			+ "                  where u.typ_uprawnien_id = 1 AND u.uzytkownik_login = g.login AND\r\n"
			+ "                       u.obiekt_id = o.id AND u.moze_przekazywac = 'T')\r\n"
			+ "           )     \r\n"
			+ "     ) can_grant_select,\r\n"
			+ "  (SELECT count(*) FROM uprawnienia u\r\n"
			+ "   WHERE u.typ_uprawnien_id = 2 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id) can_insert,\r\n"
			+ "   (SELECT count(*)\r\n"
			+ "    FROM  uzytkownik g\r\n"
			+ "    where g.login=? and \r\n"
			+ "          (G.ADMINISTRATOR='T' or\r\n"
			+ "           exists(select * from uprawnienia u\r\n"
			+ "                  where u.typ_uprawnien_id = 2 AND u.uzytkownik_login = g.login AND\r\n"
			+ "                       u.obiekt_id = o.id AND u.moze_przekazywac = 'T')\r\n"
			+ "           )     \r\n"
			+ "     ) can_grant_insert,\r\n"
			+ "  (SELECT count(*) FROM uprawnienia u\r\n"
			+ "   WHERE u.typ_uprawnien_id = 3 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id) can_update,\r\n"
			+ "   (SELECT count(*)\r\n"
			+ "    FROM  uzytkownik g\r\n"
			+ "    where g.login=? and \r\n"
			+ "          (G.ADMINISTRATOR='T' or\r\n"
			+ "           exists(select * from uprawnienia u\r\n"
			+ "                  where u.typ_uprawnien_id = 3 AND u.uzytkownik_login = g.login AND\r\n"
			+ "                       u.obiekt_id = o.id AND u.moze_przekazywac = 'T')\r\n"
			+ "           )     \r\n"
			+ "     ) can_grant_update,\r\n"
			+ "  (SELECT count(*) FROM uprawnienia u\r\n"
			+ "   WHERE u.typ_uprawnien_id = 4 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id) can_delete,\r\n"
			+ "   (SELECT count(*)\r\n"
			+ "    FROM  uzytkownik g\r\n"
			+ "    where g.login=? and \r\n"
			+ "          (G.ADMINISTRATOR='T' or\r\n"
			+ "           exists(select * from uprawnienia u\r\n"
			+ "                  where u.typ_uprawnien_id = 4 AND u.uzytkownik_login = g.login AND\r\n"
			+ "                       u.obiekt_id = o.id AND u.moze_przekazywac = 'T')\r\n"
			+ "           )     \r\n"
			+ "     ) can_grant_delete,\r\n"
			+ "     (SELECT count(*) FROM uprawnienia u\r\n"
			+ "      WHERE u.typ_uprawnien_id = 1 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id AND u.moze_przekazywac = 'T') can_select_grant_option,\r\n"
			+ "     (SELECT count(*) FROM uprawnienia u\r\n"
			+ "      WHERE u.typ_uprawnien_id = 2 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id AND u.moze_przekazywac = 'T') can_insert_grant_option,\r\n"
			+ "         (SELECT count(*) FROM uprawnienia u\r\n"
			+ "      WHERE u.typ_uprawnien_id = 3 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id AND u.moze_przekazywac = 'T') can_update_grant_option,\r\n"
			+ "         (SELECT count(*) FROM uprawnienia u\r\n"
			+ "      WHERE u.typ_uprawnien_id = 4 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id AND u.moze_przekazywac = 'T') can_delete_grant_option\r\n"
			+ "     \r\n" + "FROM obiekt o\r\n"
			+ "ORDER BY o.nazwa FOR UPDATE;\r\n" + "";

	private final String PERMISSIONS_QUERY = "SELECT\r\n"
			+ "  o.id,\r\n"
			+ "  o.nazwa,\r\n"
			+ "  (SELECT count(*) FROM uprawnienia u\r\n"
			+ "   WHERE u.typ_uprawnien_id = 1 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id) can_select,\r\n"
			+ "   (SELECT count(*)\r\n"
			+ "    FROM  uzytkownik g\r\n"
			+ "    where g.login=? and \r\n"
			+ "          (G.ADMINISTRATOR='T' or\r\n"
			+ "           exists(select * from uprawnienia u\r\n"
			+ "                  where u.typ_uprawnien_id = 1 AND u.uzytkownik_login = g.login AND\r\n"
			+ "                       u.obiekt_id = o.id AND u.moze_przekazywac = 'T')\r\n"
			+ "           )     \r\n"
			+ "     ) can_grant_select,\r\n"
			+ "  (SELECT count(*) FROM uprawnienia u\r\n"
			+ "   WHERE u.typ_uprawnien_id = 2 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id) can_insert,\r\n"
			+ "   (SELECT count(*)\r\n"
			+ "    FROM  uzytkownik g\r\n"
			+ "    where g.login=? and \r\n"
			+ "          (G.ADMINISTRATOR='T' or\r\n"
			+ "           exists(select * from uprawnienia u\r\n"
			+ "                  where u.typ_uprawnien_id = 2 AND u.uzytkownik_login = g.login AND\r\n"
			+ "                       u.obiekt_id = o.id AND u.moze_przekazywac = 'T')\r\n"
			+ "           )     \r\n"
			+ "     ) can_grant_insert,\r\n"
			+ "  (SELECT count(*) FROM uprawnienia u\r\n"
			+ "   WHERE u.typ_uprawnien_id = 3 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id) can_update,\r\n"
			+ "   (SELECT count(*)\r\n"
			+ "    FROM  uzytkownik g\r\n"
			+ "    where g.login=? and \r\n"
			+ "          (G.ADMINISTRATOR='T' or\r\n"
			+ "           exists(select * from uprawnienia u\r\n"
			+ "                  where u.typ_uprawnien_id = 3 AND u.uzytkownik_login = g.login AND\r\n"
			+ "                       u.obiekt_id = o.id AND u.moze_przekazywac = 'T')\r\n"
			+ "           )     \r\n"
			+ "     ) can_grant_update,\r\n"
			+ "  (SELECT count(*) FROM uprawnienia u\r\n"
			+ "   WHERE u.typ_uprawnien_id = 4 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id) can_delete,\r\n"
			+ "   (SELECT count(*)\r\n"
			+ "    FROM  uzytkownik g\r\n"
			+ "    where g.login=? and \r\n"
			+ "          (G.ADMINISTRATOR='T' or\r\n"
			+ "           exists(select * from uprawnienia u\r\n"
			+ "                  where u.typ_uprawnien_id = 4 AND u.uzytkownik_login = g.login AND\r\n"
			+ "                       u.obiekt_id = o.id AND u.moze_przekazywac = 'T')\r\n"
			+ "           )     \r\n"
			+ "     ) can_grant_delete,\r\n"
			+ "     (SELECT count(*) FROM uprawnienia u\r\n"
			+ "      WHERE u.typ_uprawnien_id = 1 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id AND u.moze_przekazywac = 'T') can_select_grant_option,\r\n"
			+ "     (SELECT count(*) FROM uprawnienia u\r\n"
			+ "      WHERE u.typ_uprawnien_id = 2 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id AND u.moze_przekazywac = 'T') can_insert_grant_option,\r\n"
			+ "         (SELECT count(*) FROM uprawnienia u\r\n"
			+ "      WHERE u.typ_uprawnien_id = 3 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id AND u.moze_przekazywac = 'T') can_update_grant_option,\r\n"
			+ "         (SELECT count(*) FROM uprawnienia u\r\n"
			+ "      WHERE u.typ_uprawnien_id = 4 AND\r\n"
			+ "         u.uzytkownik_login = ? AND u.obiekt_id = o.id AND u.moze_przekazywac = 'T') can_delete_grant_option\r\n"
			+ "     \r\n" + "FROM obiekt o\r\n" + "ORDER BY o.nazwa;\r\n" + "";

	private final String ALL_USERS_QUERY = "SELECT * FROM uzytkownik u WHERE u.administrator = 'N' AND u.login <> ?;";
	private final String CURRENT_USER_PERMISSIONS = "SELECT o.nazwa, t.nazwa FROM obiekt o, typ_uprawnien t, uprawnienia u "
			+ "WHERE u.uzytkownik_login = ? AND u.typ_uprawnien_id=t.id AND u.obiekt_id=o.id ;";

	public ArrayList<SimplePermissionEntry> getPermissions(String user) {
		this.connection = this.getConnection();
		ArrayList<SimplePermissionEntry> entryList = null;
		PreparedStatement statement = null;
		try {
			statement = this.connection
					.prepareStatement("SELECT administrator FROM uzytkownik WHERE login = ? ;");
			statement.setString(1, user);
			ResultSet set = statement.executeQuery();
			set.next();
			String admin = set.getString(1);

			if (admin.equalsIgnoreCase("n")) {
				statement = this.connection
						.prepareStatement(this.CURRENT_USER_PERMISSIONS);
				statement.setString(1, user);
				set = statement.executeQuery();
				entryList = new ArrayList<SimplePermissionEntry>();

				while (set.next()) {
					entryList.add(new SimplePermissionEntry(set.getString(1),
							set.getString(2)));
				}
			} else {
				entryList = new ArrayList<SimplePermissionEntry>();
				entryList.add(new SimplePermissionEntry("admin", "admin"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return entryList;
	}

	public List<PermissionEntry> loadPermissions(String grantor, String user,
			boolean lock) {
		this.connection = this.getConnection();
		List<PermissionEntry> entryList = null;
		PreparedStatement statement = null;
		try {
			if (!lock) {
				statement = this.connection
						.prepareStatement(this.PERMISSIONS_QUERY);
			} else {
				statement = this.connection
						.prepareStatement(this.PERMISSIONS_QUERY_WITH_LOCK);
			}
			int numberOfParams = 12;
			for (int i = 1; i <= 8; i++) {
				if (i % 2 != 0) {
					statement.setString(i, user);
				} else {
					statement.setString(i, grantor);
				}
			}
			for (int i = 9; i <= numberOfParams; i++) {
				statement.setString(i, user);
			}
			ResultSet set = statement.executeQuery();
			entryList = new ArrayList<PermissionEntry>();
			while (set.next()) {
				PermissionEntry entry = new PermissionEntry(set.getInt(1),
						set.getString(2), set.getInt(3), set.getInt(4),
						set.getInt(5), set.getInt(6), set.getInt(7),
						set.getInt(8), set.getInt(9), set.getInt(10),
						set.getInt(11), set.getInt(12), set.getInt(13),
						set.getInt(14));
				entryList.add(entry);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					System.err.println(e.getMessage());
				}
			}
			if (this.connection != null) {
				try {
					if (!lock) {
						this.connection.close();
					}
				} catch (SQLException e) {
					System.err.println(e.getMessage());
				}
			}
		}
		return entryList;
	}

	public List<Uzytkownik> getAllUsers(String currentUser) {
		List<Uzytkownik> usersList = null;
		Connection connection = this.getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(this.ALL_USERS_QUERY);
			statement.setString(1, currentUser);
			ResultSet set = statement.executeQuery();
			usersList = new ArrayList<Uzytkownik>();
			while (set.next()) {
				Uzytkownik uzytkownik = new Uzytkownik(set.getString(1),
						("T".equals(set.getString(2)) ? true : false));
				usersList.add(uzytkownik);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					System.err.println(e.getMessage());
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					System.err.println(e.getMessage());
				}
			}
		}
		return usersList;
	}

	private final String BEGIN_CLAUSE = "BEGIN; ";

	public void saveChanges(List<PermissionEntry> entryList, String grantor,
			String user) throws SQLException {
		// TODO: zrobic zapis uprawnien
		List<PermissionEntry> oldPermissions = this.loadPermissions(grantor,
				user, true); // pobranie poprzedniej wartosci uprawnien
		StringBuffer scriptBuffer = new StringBuffer(); // bufor skryptu
		scriptBuffer.append(BEGIN_CLAUSE);
		// usuwanie duplikatow i budowa skryptu
		for (int i = 0; i < oldPermissions.size(); i++) {
			PermissionEntry oldEntry = oldPermissions.get(i);
			PermissionEntry newEntry = entryList.get(i);
			if (!oldEntry.equals(newEntry)) {
				if (!oldEntry.getCanSelect().equals(newEntry.getCanSelect())) {
					if (newEntry.getCanSelect() == 1
							&& oldEntry.getCanSelect() == 0) {
						scriptBuffer.append(constructCanSelectInsert(user,
								grantor, newEntry.getCanSelectGrantOption(),
								newEntry.getObiektID()));
					} else if (newEntry.getCanSelect() == 0
							&& oldEntry.getCanSelect() == 1) {
						scriptBuffer.append(constructCanSelectDelete(user,
								newEntry.getObiektID()));
					}
				} else if (!oldEntry.getCanSelectGrantOption().equals(
						newEntry.getCanSelectGrantOption())) {
					scriptBuffer.append(constructCanGrantSelectUpdate(user,
							grantor, newEntry.getCanSelectGrantOption(),
							newEntry.getObiektID()));
				}

				if (!oldEntry.getCanInsert().equals(newEntry.getCanInsert())) {
					if (newEntry.getCanInsert() == 1
							&& oldEntry.getCanInsert() == 0) {
						scriptBuffer.append(constructCanInsertInsert(user,
								grantor, newEntry.getCanInsertGrantOption(),
								newEntry.getObiektID()));
					} else if (newEntry.getCanInsert() == 0
							&& oldEntry.getCanInsert() == 1) {
						scriptBuffer.append(constructCanInsertDelete(user,
								newEntry.getObiektID()));
					}
				} else if (!oldEntry.getCanInsertGrantOption().equals(
						newEntry.getCanInsertGrantOption())) {
					scriptBuffer.append(constructCanGrantInsertUpdate(user,
							grantor, newEntry.getCanInsertGrantOption(),
							newEntry.getObiektID()));
				}

				if (!oldEntry.getCanUpdate().equals(newEntry.getCanUpdate())) {
					if (newEntry.getCanUpdate() == 1
							&& oldEntry.getCanUpdate() == 0) {
						scriptBuffer.append(constructCanUpdateInsert(user,
								grantor, newEntry.getCanUpdateGrantOption(),
								newEntry.getObiektID()));
					} else if (newEntry.getCanUpdate() == 0
							&& oldEntry.getCanUpdate() == 1) {
						scriptBuffer.append(constructCanUpdateDelete(user,
								newEntry.getObiektID()));
					}
				} else if (!oldEntry.getCanUpdateGrantOption().equals(
						newEntry.getCanUpdateGrantOption())) {
					scriptBuffer.append(constructCanGrantUpdateUpdate(user,
							grantor, newEntry.getCanUpdateGrantOption(),
							newEntry.getObiektID()));
				}

				if (!oldEntry.getCanDelete().equals(newEntry.getCanDelete())) {
					if (newEntry.getCanDelete() == 1
							&& oldEntry.getCanDelete() == 0) {
						scriptBuffer.append(constructCanDeleteInsert(user,
								grantor, newEntry.getCanDeleteGrantOption(),
								newEntry.getObiektID()));
					} else if (newEntry.getCanDelete() == 0
							&& oldEntry.getCanDelete() == 1) {
						scriptBuffer.append(constructCanDeleteDelete(user,
								newEntry.getObiektID()));
					}
				} else if (!oldEntry.getCanDeleteGrantOption().equals(
						newEntry.getCanDeleteGrantOption())) {
					scriptBuffer.append(constructCanGrantDeleteUpdate(user,
							grantor, newEntry.getCanDeleteGrantOption(),
							newEntry.getObiektID()));
				}

			}
		}
		PreparedStatement statement = null;
		try {
			System.out.println(scriptBuffer.toString());
			statement = this.connection.prepareStatement(scriptBuffer
					.toString());
			statement.execute();
			this.connection.commit();
		} catch (SQLException e) {
			this.connection.rollback();
			throw e;
		} finally {
			statement.close();
			this.connection.close();
		}

	}

	private Object constructCanGrantDeleteUpdate(String user, String grantor,
			Integer canDeleteGrantOption, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer.append(" UPDATE uprawnienia SET moze_przekazywac = ");
		queryBuffer.append(canDeleteGrantOption > 0 ? "'T'" : "'N'");
		queryBuffer.append(" , grantor_login = '");
		queryBuffer.append(grantor);
		queryBuffer.append("' , czas_przekazania = localtimestamp");
		queryBuffer.append(" WHERE uzytkownik_login = '");
		queryBuffer.append(user);
		queryBuffer.append("' AND typ_uprawnien_id = 4");
		queryBuffer.append(" AND obiekt_id = ");
		queryBuffer.append(obiektID);
		queryBuffer.append(";");
		return queryBuffer.toString();
	}

	private Object constructCanDeleteDelete(String user, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer
				.append(" DELETE FROM uprawnienia WHERE typ_uprawnien_id = 4 AND");
		queryBuffer.append(" uzytkownik_login = '");
		queryBuffer.append(user);
		queryBuffer.append("' AND obiekt_id = ");
		queryBuffer.append(obiektID);
		queryBuffer.append(";");
		return queryBuffer.toString();
	}

	private Object constructCanDeleteInsert(String user, String grantor,
			Integer canDeleteGrantOption, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer
				.append(" INSERT INTO uprawnienia(typ_uprawnien_id, uzytkownik_login, grantor_login, moze_przekazywac, czas_przekazania, obiekt_id) VALUES(");
		queryBuffer.append("4,'");
		queryBuffer.append(user);
		queryBuffer.append("','");
		queryBuffer.append(grantor);
		queryBuffer.append("',");
		queryBuffer.append(canDeleteGrantOption > 0 ? "'T'" : "'N'");
		queryBuffer.append(",");
		queryBuffer.append(" localtimestamp");
		queryBuffer.append(",");
		queryBuffer.append(obiektID);
		queryBuffer.append("); ");
		return queryBuffer.toString();
	}

	private Object constructCanGrantUpdateUpdate(String user, String grantor,
			Integer canUpdateGrantOption, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer.append(" UPDATE uprawnienia SET moze_przekazywac = ");
		queryBuffer.append(canUpdateGrantOption > 0 ? "'T'" : "'N'");
		queryBuffer.append(" , grantor_login = '");
		queryBuffer.append(grantor);
		queryBuffer.append("' , czas_przekazania = localtimestamp");
		queryBuffer.append(" WHERE uzytkownik_login = '");
		queryBuffer.append(user);
		queryBuffer.append("' AND typ_uprawnien_id = 3");
		queryBuffer.append("AND obiekt_id = ");
		queryBuffer.append(obiektID);
		queryBuffer.append(";");
		return queryBuffer.toString();
	}

	private Object constructCanUpdateDelete(String user, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer
				.append(" DELETE FROM uprawnienia WHERE typ_uprawnien_id = 3 AND");
		queryBuffer.append(" uzytkownik_login = '");
		queryBuffer.append(user);
		queryBuffer.append("' AND obiekt_id = ");
		queryBuffer.append(obiektID);
		queryBuffer.append(";");
		return queryBuffer.toString();
	}

	private String constructCanUpdateInsert(String user, String grantor,
			Integer canUpdateGrantOption, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer
				.append(" INSERT INTO uprawnienia(typ_uprawnien_id, uzytkownik_login, grantor_login, moze_przekazywac, czas_przekazania, obiekt_id) VALUES(");
		queryBuffer.append("3,'");
		queryBuffer.append(user);
		queryBuffer.append("','");
		queryBuffer.append(grantor);
		queryBuffer.append("',");
		queryBuffer.append(canUpdateGrantOption > 0 ? "'T'" : "'N'");
		queryBuffer.append(",");
		queryBuffer.append(" localtimestamp");
		queryBuffer.append(",");
		queryBuffer.append(obiektID);
		queryBuffer.append("); ");
		return queryBuffer.toString();
	}

	private String constructCanGrantInsertUpdate(String user, String grantor,
			Integer canInsertGrantOption, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer.append(" UPDATE uprawnienia SET moze_przekazywac = ");
		queryBuffer.append(canInsertGrantOption > 0 ? "'T'" : "'N'");
		queryBuffer.append(" , grantor_login = '");
		queryBuffer.append(grantor);
		queryBuffer.append("' , czas_przekazania = localtimestamp");
		queryBuffer.append(" WHERE uzytkownik_login = '");
		queryBuffer.append(user);
		queryBuffer.append("' AND typ_uprawnien_id = 2");
		queryBuffer.append(" AND obiekt_id = ");
		queryBuffer.append(obiektID);
		queryBuffer.append(";");
		return queryBuffer.toString();
	}

	private String constructCanInsertDelete(String user, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer
				.append(" DELETE FROM uprawnienia WHERE typ_uprawnien_id = 2 AND");
		queryBuffer.append(" uzytkownik_login = '");
		queryBuffer.append(user);
		queryBuffer.append("' AND obiekt_id = ");
		queryBuffer.append(obiektID);
		queryBuffer.append(";");
		return queryBuffer.toString();
	}

	private String constructCanInsertInsert(String user, String grantor,
			Integer canInsertGrantOption, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer
				.append(" INSERT INTO uprawnienia(typ_uprawnien_id, uzytkownik_login, grantor_login, moze_przekazywac, czas_przekazania, obiekt_id) VALUES(");
		queryBuffer.append("2,'");
		queryBuffer.append(user);
		queryBuffer.append("','");
		queryBuffer.append(grantor);
		queryBuffer.append("',");
		queryBuffer.append(canInsertGrantOption > 0 ? "'T'" : "'N'");
		queryBuffer.append(",");
		queryBuffer.append(" localtimestamp");
		queryBuffer.append(",");
		queryBuffer.append(obiektID);
		queryBuffer.append("); ");
		return queryBuffer.toString();
	}

	private String constructCanSelectDelete(String user, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer
				.append(" DELETE FROM uprawnienia WHERE typ_uprawnien_id = 1 AND");
		queryBuffer.append(" uzytkownik_login = '");
		queryBuffer.append(user);
		queryBuffer.append("' AND obiekt_id = ");
		queryBuffer.append(obiektID);
		queryBuffer.append(";");
		return queryBuffer.toString();
	}

	private String constructCanGrantSelectUpdate(String user, String grantor,
			Integer canSelectGrantOption, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer.append(" UPDATE uprawnienia SET moze_przekazywac = ");
		queryBuffer.append(canSelectGrantOption > 0 ? "'T'" : "'N'");
		queryBuffer.append(" , grantor_login = '");
		queryBuffer.append(grantor);
		queryBuffer.append("' , czas_przekazania = localtimestamp");
		queryBuffer.append(" WHERE uzytkownik_login = '");
		queryBuffer.append(user);
		queryBuffer.append("' AND typ_uprawnien_id = 1");
		queryBuffer.append(" AND obiekt_id = ");
		queryBuffer.append(obiektID);
		queryBuffer.append(";");
		return queryBuffer.toString();
	}

	private String constructCanSelectInsert(String user, String grantor,
			Integer canSelectGrantOption, Integer obiektID) {
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer
				.append(" INSERT INTO uprawnienia(typ_uprawnien_id, uzytkownik_login, grantor_login, moze_przekazywac, czas_przekazania, obiekt_id) VALUES(");
		queryBuffer.append("1,'");
		queryBuffer.append(user);
		queryBuffer.append("','");
		queryBuffer.append(grantor);
		queryBuffer.append("',");
		queryBuffer.append(canSelectGrantOption > 0 ? "'T'" : "'N'");
		queryBuffer.append(",");
		queryBuffer.append(" localtimestamp");
		queryBuffer.append(",");
		queryBuffer.append(obiektID);
		queryBuffer.append("); ");
		return queryBuffer.toString();
	}

	public ArrayList<UprawnieniaSimple> getUserPermissions(String username) {
		Connection connection = this.getConnection();
		ArrayList<UprawnieniaSimple> list = null;
		PreparedStatement statement = null;
		try {
			statement = connection
					.prepareStatement("SELECT id, typ_uprawnien_id, grantor_login, "
							+ "moze_przekazywac, czas_przekazania, obiekt_id FROM Uprawnienia "
							+ "WHERE uzytkownik_login like ? ");
			statement.setString(1, username);
			ResultSet set = statement.executeQuery();
			list = new ArrayList<UprawnieniaSimple>();
			while (set.next()) {
				list.add(new UprawnieniaSimple(set.getInt(1), set.getInt(2),
						username, set.getString(3), set.getBoolean(4), set
								.getDate(5), set.getInt(6)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	public void deleteUserPermissions(String username) {
		Connection connection = this.getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement("DELETE FROM uprawnienia "
					+ "WHERE uzytkownik_login like ? ");
			statement.setString(1, username);
			statement.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	public void setAdmin(boolean admin, String username) {
		Connection connection = this.getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection
					.prepareStatement("UPDATE uzytkownik SET administrator = ? "
							+ "WHERE login like ?");
			statement.setString(1, admin ? "T" : "N");
			statement.setString(2, username);
			statement.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	public boolean isAdministrator(String username) {
		Connection connection = this.getConnection();
		PreparedStatement statement = null;
		boolean admin = false;
		try {
			statement = connection
					.prepareStatement("SELECT administrator FROM uzytkownik "
							+ "WHERE login like ?");
			statement.setString(1, username);
			ResultSet set = statement.executeQuery();
			while (set.next()) {
				if (set.getString(1).equals("T"))
					admin = true;
				else
					admin = false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return admin;
	}

	public void updatePermission(UprawnieniaSimple u) {
		Connection connection = this.getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection
					.prepareStatement("UPDATE uprawnienia SET "
							+ "uzytkownik_login = ?, grantor_login = ?, czas_przekazania = localtimestamp "
							+ "WHERE id = ?");
			statement.setString(1, u.getUzytkownik());
			statement.setString(2, u.getGrantor());
			statement.setInt(3, u.getId());
			statement.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	public boolean hasPrzejmij(String username) {
		Connection connection = this.getConnection();
		PreparedStatement statement = null;
		boolean przejmij = false;
		try {
			statement = connection
					.prepareStatement("SELECT COUNT(*) FROM uprawnienia u "
							+ "WHERE u.uzytkownik_login = ? AND typ_uprawnien_id = 5");
			statement.setString(1, username);
			ResultSet set = statement.executeQuery();
			set.next();
			if (set.getInt(1) > 0)
				przejmij = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return przejmij;
	}
	public void addPrzejmij(String username, String grantor) {
		Connection connection = this.getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection
					.prepareStatement("INSERT INTO uprawnienia(\r\n" + 
							"            typ_uprawnien_id, uzytkownik_login, grantor_login, moze_przekazywac, \r\n" + 
							"            czas_przekazania, obiekt_id)\r\n" + 
							"    VALUES (5, ?, ?, 'N', localtimestamp, \r\n" + 
							"            1);");
			statement.setString(1, username);
			statement.setString(2, grantor);
			statement.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					if(connection != null)
						try {
							connection.rollback();
						} catch (SQLException e1) {
							// TODO Auto-generated catch block
							e1.printStackTrace();
						}
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	public void deletePrzejmij(String username) {
		Connection connection = this.getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection
					.prepareStatement("DELETE FROM uprawnienia WHERE typ_uprawnien_id = 5 " +
							"AND uzytkownik_login = ?");
			statement.setString(1, username);
			statement.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					if(connection != null)
						try {
							connection.rollback();
						} catch (SQLException e1) {
							// TODO Auto-generated catch block
							e1.printStackTrace();
						}
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
}
